package com.augurit.gzcgwsp.excelimportexport.service.impl;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.Region;
import org.dozer.DozerBeanMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.augurit.gzcgwsp.excelimportexport.dao.YwRqQyjyxkRqqxdylbDao;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkQysbclmc;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqqxdylb;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqqxdylbService;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqqxdylb;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqqxssdjb;
import com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkFinalRqqxdylbService;

/**
 * 广州市燃气经营许可证许可审批------三、1 燃气抢险点一览表
 * @author xgw
 *
 */
@Service
@Transactional
public class YwRqQyjyxkRqqxdylbServiceImpl implements IYwRqQyjyxkRqqxdylbService {

	@Autowired
	YwRqQyjyxkRqqxdylbDao ywRqQyjyxkRqqxdylbDao;
	@Autowired
	IYwRqQyjyxkFinalRqqxdylbService qxdylService;
	/**
	 * 保存 三、1 燃气抢险点一览表
	 * @param ywRqQyjyxkRqqxdylb
	 */
	public void save(YwRqQyjyxkRqqxdylb ywRqQyjyxkRqqxdylb) {
		ywRqQyjyxkRqqxdylbDao.save(ywRqQyjyxkRqqxdylb);
	}
	
	/**
	 * 根据企业id获取    燃气抢险点一览表
	 * @param qyid
	 * @return
	 */
	private List<YwRqQyjyxkRqqxdylb> listByQyid(Long qyid,String acceptNo){
	    String hql = "from YwRqQyjyxkRqqxdylb t where t.qyid='" + qyid + "' and t.acceptNo='"+acceptNo+"'";
        List<YwRqQyjyxkRqqxdylb> entityList = ywRqQyjyxkRqqxdylbDao.find(hql);
        return entityList;
	}
	
	/**
	 * 解析 三、1 燃气抢险点一览表
	 * @param hssfsheet
	 * @param acceptNo
	 * @param qyid
	 * @return
	 */
	public YwRqQyjyxkRqqxdylb parseRqqxdylb(HSSFSheet hssfsheet,String acceptNo, Long qyid, String username) {
		
		String xh = null;//序号
		String mc = null;//名称
		String dz = null;//地址
		String dh = null;//电话
		
		String hql = "from YwRqQyjyxkRqqxdylb t where t.qyid = "+qyid+"";
			List<YwRqQyjyxkRqqxdylb> list=ywRqQyjyxkRqqxdylbDao.find(hql);
			for(int i=0;i<list.size();i++)
			{
				ywRqQyjyxkRqqxdylbDao.delete(list.get(i));
			}
		try {

			HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
			// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
			for (int j = 7; j < hssfsheet.getPhysicalNumberOfRows(); j++) {

				hssfrow = hssfsheet.getRow(j);
				// 判断是否还存在需要导入的数据
				if (hssfrow == null) {
					// System.out.println("这里已没有数据，在第" + i + "列,第" + j + "行");
					break;
				}
				
				if(j >= 7){
					if (hssfrow.getCell((short) 0) == null) {
						xh = "";
					} else if (hssfrow.getCell((short) 0).getCellType() == 0) {
						xh = new Double(hssfrow.getCell((short) 0).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						xh = hssfrow.getCell((short) 0).getStringCellValue().trim();
					}
					if (hssfrow.getCell((short) 1) == null) {
						mc = "";
					} else if (hssfrow.getCell((short) 1).getCellType() == 0) {
						mc = new Double(hssfrow.getCell((short) 1).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						mc = hssfrow.getCell((short) 1).getStringCellValue().trim();
					}
					if (hssfrow.getCell((short) 3) == null) {
						dz = "";
					} else if (hssfrow.getCell((short) 3).getCellType() == 0) {
						dz = new Double(hssfrow.getCell((short) 3).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						dz = hssfrow.getCell((short) 3).getStringCellValue().trim();
					}
					if (hssfrow.getCell((short) 5) == null) {
						dh = "";
					} else if (hssfrow.getCell((short) 5).getCellType() == 0) {
						dh = new Double(hssfrow.getCell((short) 5).getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						dh = hssfrow.getCell((short) 5).getStringCellValue().trim();
					}
					
					
//					if(StringUtils.isEmpty(xh)){
//						break;
					if(xh.equals("燃气抢险设备一览表"))
					{
						break;
					}else{
					    if(StringUtils.isNotBlank(mc) && StringUtils.isNotBlank(dz)){
					        YwRqQyjyxkRqqxdylb ywRqQyjyxkRqqxdylb = new YwRqQyjyxkRqqxdylb();
	                        ywRqQyjyxkRqqxdylb.setQyid(qyid);
	                        ywRqQyjyxkRqqxdylb.setAcceptNo(acceptNo);
	                        ywRqQyjyxkRqqxdylb.setUserName(username);
	                        ywRqQyjyxkRqqxdylb.setUploadDate(new Date());
	                        ywRqQyjyxkRqqxdylb.setXh(xh);
	                        ywRqQyjyxkRqqxdylb.setMc(mc);
	                        ywRqQyjyxkRqqxdylb.setDz(dz);
	                        ywRqQyjyxkRqqxdylb.setDh(dh);
	                        this.save(ywRqQyjyxkRqqxdylb);
					    }
						
					}
					
				}
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	
		return null;
	}
	
	/**
     * 导出 燃气抢险点一览表
     * @param hssfsheet
     * @param acceptNo
     * @param qyid
     * @return
     */
    public HSSFSheet expRqqxdylb(HSSFSheet hssfsheet,String acceptNo, Long qyid, String username,HSSFCellStyle cs,String exptype) {
    	if("qyxx".equals(exptype)){
    		 List<YwRqQyjyxkFinalRqqxdylb> entityList = this.qxdylService.getInfosByQyid(qyid);
    	        if(entityList == null || entityList.size() <= 0) return null;
    	        if(entityList!=null){
	    	        try {
	    	            /**
	    	             * i代表生成的Excel中列表行，j表示将要产生多少行数据
	    	             */
	    	            for(int i = 7, j = 0; j< entityList.size(); i++){
	    	               // if(i < 7) continue;
	    	            	YwRqQyjyxkFinalRqqxdylb entity = entityList.get(j);
	    	                if(entity != null){
	    	                	if(StringUtils.isNotBlank(entity.getMc())){
	    			                    HSSFRow row = hssfsheet.createRow(i);// 创建一个行对象
	    			                    HSSFCell cell0 = row.createCell(0);// 在本行的第一个单元格中写入数据
	    			                    HSSFRichTextString text0 = new HSSFRichTextString(entity.getXh()==null?"":entity.getXh());// 声明文本内容
	    			                    cell0.setCellValue(text0);// 将文本对象加入单元格
	    			                    cell0.setCellStyle(cs); 
	    			                    
	    			                   hssfsheet.addMergedRegion(new Region(1,(short)1,1,(short)2)); // 四个参数分别是：起始行，起始列，结束行，结束列
	    			                    HSSFCell cell1 = row.createCell(1);// 在本行的第一个单元格中写入数据
	    			                    HSSFCell cell2 = row.createCell(2);
	    			                    HSSFRichTextString text1 = new HSSFRichTextString(entity.getMc()==null?"":entity.getMc());// 声明文本内容
	    			                    cell1.setCellValue(text1);// 将文本对象加入单元格
	    			                    cell1.setCellStyle(cs); 
	    			                    cell2.setCellStyle(cs); 
	    			                    
	    			                   hssfsheet.addMergedRegion(new Region(1,(short)3,1,(short)4)); // 四个参数分别是：起始行，起始列，结束行，结束列
	    			                    HSSFCell cell3 = row.createCell(3);// 在本行的第一个单元格中写入数据
	    			                    HSSFCell cell4 = row.createCell(4);
	    			                    HSSFRichTextString text3 = new HSSFRichTextString(entity.getDz()==null?"":entity.getDz());// 声明文本内容
	    			                    cell3.setCellValue(text3);// 将文本对象加入单元格
	    			                    cell3.setCellStyle(cs); 
	    			                    cell4.setCellStyle(cs);
	    			                    HSSFCell cell5 = row.createCell(5);// 在本行的第一个单元格中写入数据
	    			                    HSSFRichTextString text5 = new HSSFRichTextString(entity.getDh()==null?"":entity.getDh());// 声明文本内容
	    			                    cell5.setCellValue(text5);// 将文本对象加入单元格
	    			                    cell5.setCellStyle(cs); 
	    	                	}
	    	                }
	    	                j++;
	    	            }
	    	        } catch (Exception e) {
	    	            e.printStackTrace();
	    	        }
    	        }
    	}else{
    		 List<YwRqQyjyxkRqqxdylb> entityList = this.listByQyid(qyid,acceptNo);
    	        if(entityList == null || entityList.size() <= 0) return null;
    	        if(entityList!=null)
    	        {
    	        try {
    	            /**
    	             * i代表生成的Excel中列表行，j表示将要产生多少行数据
    	             */
    	            for(int i = 7, j = 0; j< entityList.size(); i++){
    	               // if(i < 7) continue;
    	                YwRqQyjyxkRqqxdylb entity = entityList.get(j);
    	                if(entity != null){
    	                	if(StringUtils.isNotBlank(entity.getMc())){
    			                    HSSFRow row = hssfsheet.createRow(i);// 创建一个行对象
    			                    HSSFCell cell0 = row.createCell(0);// 在本行的第一个单元格中写入数据
    			                    HSSFRichTextString text0 = new HSSFRichTextString(entity.getXh()==null?"":entity.getXh());// 声明文本内容
    			                    cell0.setCellValue(text0);// 将文本对象加入单元格
    			                    cell0.setCellStyle(cs); 
    			                    
    			                   hssfsheet.addMergedRegion(new Region(1,(short)1,1,(short)2)); // 四个参数分别是：起始行，起始列，结束行，结束列
    			                    HSSFCell cell1 = row.createCell(1);// 在本行的第一个单元格中写入数据
    			                    HSSFCell cell2 = row.createCell(2);
    			                    HSSFRichTextString text1 = new HSSFRichTextString(entity.getMc()==null?"":entity.getMc());// 声明文本内容
    			                    cell1.setCellValue(text1);// 将文本对象加入单元格
    			                    cell1.setCellStyle(cs); 
    			                    cell2.setCellStyle(cs); 
    			                    
    			                   hssfsheet.addMergedRegion(new Region(1,(short)3,1,(short)4)); // 四个参数分别是：起始行，起始列，结束行，结束列
    			                    HSSFCell cell3 = row.createCell(3);// 在本行的第一个单元格中写入数据
    			                    HSSFCell cell4 = row.createCell(4);
    			                    HSSFRichTextString text3 = new HSSFRichTextString(entity.getDz()==null?"":entity.getDz());// 声明文本内容
    			                    cell3.setCellValue(text3);// 将文本对象加入单元格
    			                    cell3.setCellStyle(cs); 
    			                    cell4.setCellStyle(cs);
    			                    HSSFCell cell5 = row.createCell(5);// 在本行的第一个单元格中写入数据
    			                    HSSFRichTextString text5 = new HSSFRichTextString(entity.getDh()==null?"":entity.getDh());// 声明文本内容
    			                    cell5.setCellValue(text5);// 将文本对象加入单元格
    			                    cell5.setCellStyle(cs); 
    	                	}
    	                }
    	                j++;
    	            }
    	        } catch (Exception e) {
    	            e.printStackTrace();
    	        }
    	}
        }
        return hssfsheet;
    }

    public void importFinalQyData(String acceptNo, Long qyid) {
        List<YwRqQyjyxkRqqxdylb> list=this.getDatas(acceptNo, qyid);
        for(YwRqQyjyxkRqqxdylb entity:list){
            YwRqQyjyxkFinalRqqxdylb finalentity = new DozerBeanMapper().map(entity, YwRqQyjyxkFinalRqqxdylb.class);
            finalentity.setId(null);
            this.qxdylService.save(finalentity);
        }
    }
    
    /**
     * 
     * @return
     */
    private List<YwRqQyjyxkRqqxdylb> getDatas(String acceptNo, Long qyid){
        String hql = "from YwRqQyjyxkRqqxdylb t where t.qyid='" + qyid + "' and t.acceptNo='"+acceptNo+"'";
        return this.ywRqQyjyxkRqqxdylbDao.find(hql);
    }
    

}
